import pandas as pd
import numpy as np
import sqlite3
import requests
import json
from os.path import exists
from datetime import datetime
import time


# 配置管理类，用于读取和修改config.json配置信息
class ConfigManager:
    """
    属性:
        config_path: 配置文件路径，默认为 config.json
        config: 存储配置数据的字典

    方法:
        load_config: 加载配置文件内容
        get_value: 获取指定配置项的值
        set_value: 设置指定配置项的值
        save_config: 将当前配置保存到文件
    """

    # 初始化配置管理器
    def __init__(self, config_path='config.json'):
        self.config_path = config_path
        self.config = self.load_config()

    # 读取配置文件内容
    def load_config(self):
        """
        返回:
            dict: 配置数据字典，如果读取失败返回空字典
        """
        try:
            if exists(self.config_path):
                with open(self.config_path, 'r', encoding='utf-8') as f:
                    return json.load(f)
            return {}
        except Exception as e:
            print(f"⚠️ 读取配置失败: {str(e)}")
            return {}

    #  获取指定配置项的值
    def get_value(self, key, default=None):
        """
        参数:
            key: 配置项键名
            default: 如果配置项不存在时返回的默认值

        返回:
            任意类型: 配置项对应的值
        """
        return self.config.get(key, default)

    # 设置指定配置项的值
    def set_value(self, key, value):
        """
        参数:
            key: 配置项键名
            value: 要设置的值

        返回:
            bool: 配置保存是否成功
        """
        self.config[key] = value
        try:
            with open(self.config_path, 'w', encoding='utf-8') as f:
                json.dump(self.config, f, ensure_ascii=False, indent=4)
            return True
        except Exception as e:
            print(f"⚠️ 设置配置失败: {str(e)}")
            return False


# 定义类的静态方法：股票代码列表中有无市场后缀转换方法：StockCodeConversion.add/remove
class StockCodeConversion:
    """
    添加或去除市场后缀
    输入：股票代码列表
    输出：股票代码列表
    """
    # 给股票代码列表中的所有代码添加市场后缀
    @staticmethod
    def add(stock_codes):
        conn = sqlite3.connect('db_file.db')
        cursor = conn.cursor()
        cursor.execute("SELECT stock_code FROM stock_info")
        stock_codes_all = cursor.fetchall()
        conn.close()
        stock_codes_all = [code[0] for code in stock_codes_all]
        stock_codes_new = []
        if type(stock_codes) != list:
            stock_codes = [stock_codes]
        for i in stock_codes:
            if i in stock_codes_all:
                stock_codes_new.append(i)
            else:
                if (i + ".SH") in stock_codes_all:stock_codes_new.append(i + ".SH")
                elif (i + ".SZ") in stock_codes_all:stock_codes_new.append(i + ".SZ")
        return stock_codes_new

    # 将股票代码列表中的所有代码去除市场后缀
    @staticmethod
    def remove(stock_codes):
        stock_codes_new = [i[:6] for i in stock_codes]
        return stock_codes_new


# 数据库初始化
def create_tables_if_not_exist():
    try:
        with sqlite3.connect('db_file.db') as conn:
            cursor = conn.cursor()
            # 创建stocks_info表（如果不存在）
            cursor.execute("""
                    -- 创建股票基础信息表
                    CREATE TABLE IF NOT EXISTS stock_info (
                        -- 基本信息
                        stock_code         TEXT PRIMARY KEY,       -- 股票代码（唯一主键）
                        stock_name         TEXT NOT NULL,          -- 股票名称
                        list_date          TEXT,                   -- 上市日期 (格式: YYYY-MM-DD)
                        is_watchlist       INTEGER DEFAULT 0,      -- 是否自选股 (0=否, 1=是)
                        -- 财务指标（省略部分字段便于展示）
                        roe_ttm            REAL,                   -- 净资产收益率 (Return on Equity, TTM)
                        net_profit_margin  REAL,                   -- 净利率 (Net Profit Margin)
                        total_shares       BIGINT,                 -- 总股本 (Total Shares)
                        float_shares       BIGINT,                 -- 流通股本 (Floating Shares)
                        -- 盈利能力
                        pe_ttm             REAL,                   -- 市盈率 (Price-to-Earnings, TTM)
                        pb_ttm             REAL,                   -- 市净率 (Price-to-Book, TTM)
                        ps_ttm             REAL,                   -- 市销率 (Price-to-Sales, TTM)
                        pc_ttm             REAL,                   -- 市现率 (Price-to-Cash Flow, TTM)
                        -- 成长能力
                        dividend_yield     REAL,                   -- 股息率 (Dividend Yield)
                        book_value_per     REAL,                   -- 每股净资产 (Book Value per Share)
                        debt_to_asset      REAL,                   -- 资产负债率 (Debt-to-Asset Ratio)
                        cash_flow_per      REAL,                   -- 每股现金流 (Cash Flow per Share)
                        current_ratio      REAL                    -- 流动比率 (Current Ratio)
                    );""")
            
            # 创建stock_daily日线数据表（如果不存在）
            cursor.execute('''
                    CREATE TABLE IF NOT EXISTS stock_daily (
                        stock_code      TEXT NOT NULL,       -- 股票代码（如 600000.SH）
                        trade_date      TEXT NOT NULL,       -- 交易日期（格式: YYYY-MM-DD）
                        open_price      REAL,                -- 开盘价（元）
                        high_price      REAL,                -- 最高价（元）
                        low_price       REAL,                -- 最低价（元）
                        close_price     REAL,                -- 收盘价（元）
                        volume          BIGINT,              -- 成交量（股）
                        turnover        REAL,                -- 成交额（元）
                        prev_close      REAL,                -- 前收盘价（元）
                        -- 涨跌幅自计算字段
                        change_percent  REAL GENERATED ALWAYS AS (ROUND((close_price - prev_close) / prev_close * 100, 2)) STORED,
                        -- 振幅自计算字段
                        amplitude       REAL GENERATED ALWAYS AS (ROUND((high_price - low_price) / prev_close * 100, 2)) STORED,
                        PRIMARY KEY (stock_code, trade_date) -- 复合主键：股票+日期唯一
                        );''')
            
            # 创建stock_technical表（如果不存在）
            cursor.execute("""
                    -- 创建股票每日交易指标表（如果不存在）
                    CREATE TABLE IF NOT EXISTS stock_technical (
                        stock_code      TEXT NOT NULL,       -- 股票代码（如 600000）
                        trade_date      TEXT NOT NULL,       -- 交易日期（格式: YYYY-MM-DD）
                        -- 移动平均线 (Moving Average)
                        MA5             REAL,                -- 5日均线
                        MA10            REAL,                -- 10日均线
                        MA20            REAL,                -- 20日均线
                        MA30            REAL,                -- 30日均线
                        MA60            REAL,                -- 60日均线
                        MA120           REAL,                -- 120日均线
                        MA250           REAL,                -- 250日均线
                        Q               REAL,                -- 近250日股价分位数
                        R               REAL,                -- 当前股价与近20日最低价的涨幅
                        HIGH            INTEGER DEFAULT 0,   -- 当前股价是否高位（1 是 0 否）
                        volatility      REAL,                -- 年化波动率（30日）
                        conv            REAL,                -- 均线粘合度（5、10、20、30、60）
                        CHANGE30        REAL,                -- 30日涨跌幅
                        Angle_Composite REAL,                -- 均线角度化斜率（5、10、20、30、60）
                        Angle_State     Text,                -- 均线角度状态（5、10、20、30、60）
                        -- MACD 指标
                        DIF             REAL,                -- 差离值（12日EMA - 26日EMA）
                        DEA             REAL,                -- 差离平均值（DIF的9日EMA）
                        MACD            REAL,                -- MACD柱状图（2 * (DIF - DEA)）
                        -- 布林带 (Bollinger Bands)
                        BOLL            REAL,                -- 带宽
                        UB              REAL,                -- 上轨（BOLL + 2σ）
                        LB              REAL,                -- 下轨（BOLL - 2σ）
                        -- 相对强弱指数 (RSI)
                        RSI6            REAL,                -- 6日RSI
                        RSI12           REAL,                -- 12日RSI
                        RSI24           REAL,                -- 24日RSI
                        -- KDJ 指标
                        K               REAL,                -- 快速随机指标
                        D               REAL,                -- 慢速随机指标
                        J               REAL,                -- 超快随机指标
                        -- 顺势指标 (CCI)
                        CCI             REAL,                -- 14日CCI
                        -- 乖离率 (BIAS)
                        BIAS5           REAL,                -- 6日乖离率
                        BIAS10          REAL,                -- 12日乖离率
                        BIAS20          REAL,                -- 24日乖离率
                        -- 成交量指标
                        VOL5            REAL,                -- 5日成交量均值
                        VOL10           REAL,                -- 10日成交量均值
                        VOL20           REAL,                -- 20日成交量均值
                        VOL30           REAL,                -- 30日成交量均值
                        VOL60           REAL,                -- 60日成交量均值
                        VOL120          REAL,                -- 120日成交量均值
                        VOL250          REAL,                -- 250日成交量均值
                        --TURNOVER_RATE   REAL,                -- 换手率
                        VOLR            REAL,                -- 量比
                        -- K线形态
                        is_high_doji            INTEGER DEFAULT 0,   -- 是否为高位十字星
                        is_volume_stagnation    INTEGER DEFAULT 0,   -- 是否为高位放量滞涨
                        is_high_long_shadow     INTEGER DEFAULT 0,   -- 是否为高位长影线
                        is_high_volume_decline  INTEGER DEFAULT 0,   -- 是否为高位放量大跌
                        is_high_long_bearish    INTEGER DEFAULT 0,   -- 是否为高位放量长阴
                        is_high_triple_bearish  INTEGER DEFAULT 0,   -- 是否为高位三重阴
                        is_high_volume_price    INTEGER DEFAULT 0,   -- 是否为量价顶背离

                        FOREIGN KEY(stock_code) REFERENCES stock_daily(stock_code),
                        PRIMARY KEY (stock_code, trade_date)
                    );""")
            
            # 创建backtest数据表（如果不存在）
            cursor.execute('''
                    CREATE TABLE IF NOT EXISTS backtest (
                        stock_code      TEXT NOT NULL PRIMARY KEY,    -- 股票代码（如 600000）
                        默认策略        REAL                          -- 默认策略收益率
                        );''')
            
            # 创建trade_dates表（如果不存在）
            cursor.execute("CREATE TABLE IF NOT EXISTS trade_dates (trade_date TEXT PRIMARY KEY)")
            
            conn.commit()
            print("数据库创建成功")
    except sqlite3.Error as e:
        print(f"⚠️ 数据库操作失败: {str(e)}")


# 获取所有股票名称和代码，返回包含股票代码（带后缀）和名称的元组列表
def get_all_stocks_info():
    """
    从stock_info表中获取所有股票信息（代码和名称）
    
    返回:
        tuple: 包含(stock_code, stock_name)元组的列表
    """
    conn = sqlite3.connect('db_file.db')
    cursor = conn.cursor()
    
    try:
        # 执行查询
        cursor.execute("SELECT stock_code, stock_name FROM stock_info")
        # 获取所有结果
        results = cursor.fetchall()
        # 返回包含股票代码和名称的元组列表
        return results
    except sqlite3.Error as e:
        print(f"⚠️ 数据库操作失败: {str(e)}")
        return tuple()
    finally:
        cursor.close()
        conn.close()


# 获取当前自选股列表，返回包含股票代码（带后缀）和名称的元组列表
def get_watchlist(number:int=1):
    conn = sqlite3.connect('db_file.db')
    cursor = conn.cursor()
    cursor.execute("SELECT stock_code, stock_name FROM stock_info WHERE is_watchlist = ?", (number,))
    watchlist = cursor.fetchall()
    conn.close()
    return watchlist


# 根据查询条件搜索股票，返回包含股票代码（带后缀）和名称的元组列表
def search_stock(query):
    """
    如果输入是6位数字则按股票代码前6位查询，否则按股票名称查询
    """
    conn = sqlite3.connect('db_file.db')
    cursor = conn.cursor()

    # 如果输入是6位数字，则按股票代码前6位查询
    if query.isdigit() and len(query) == 6:
        cursor.execute("SELECT stock_code, stock_name FROM stock_info WHERE substr(stock_code, 1, 6) = ?", (query,))
    # 否则按股票名称模糊查询
    else:
        cursor.execute("SELECT stock_code, stock_name FROM stock_info WHERE stock_name LIKE ?", (f"%{query}%",))

    results = cursor.fetchall()
    conn.close()
    return results

# 查询指定股票指定日期的收盘价
def get_stock_price(stock_code, stock_date):
    with sqlite3.connect('db_file.db') as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT close_price FROM stock_daily WHERE stock_code =? AND trade_date =?", (stock_code, stock_date))
        result = cursor.fetchone()
        return round(float(result[0]), 2) if result else None

# 查询指定股票指定日期的上一条记录的收盘价
def get_previous_price(stock_code, stock_date):
    with sqlite3.connect('db_file.db') as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT close_price FROM stock_daily WHERE stock_code =? AND trade_date < ? ORDER BY trade_date DESC LIMIT 1", (stock_code, stock_date))
        result = cursor.fetchone()
        return round(float(result[0]), 2) if result else None

# 查询指定股票指定日期的下一条记录的开盘价
def get_next_price(stock_code, stock_date):
    with sqlite3.connect('db_file.db') as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT open_price FROM stock_daily WHERE stock_code =? AND trade_date > ? ORDER BY trade_date ASC LIMIT 1", (stock_code, stock_date))
        result = cursor.fetchone()
        return round(float(result[0]), 2) if result else None

# 更新股票列表
def update_stock_list():
    """
    从 config.json 中读取 stock_info_url，获取股票列表数据，
    并将 dm、mc 分别更新到 stock_info 表的 stock_code 和 stock_name 字段
    """
    try:
        config = ConfigManager()
        stock_info_url = config.get_value('stock_info_url').replace("您的licence", config.get_value('licence'))

        if not stock_info_url:
            print("未在 config.json 中找到 stock_info_url")
            return

        # 发送请求获取数据
        response = requests.get(stock_info_url)
        response.raise_for_status()  # 检查请求是否成功

        # 解析 JSON 数据
        stock_data = response.json()

        # 连接数据库
        conn = sqlite3.connect('db_file.db')
        cursor = conn.cursor()

        cursor.execute("SELECT stock_code FROM stock_info")
        stock_list = [stock[0] for stock in cursor.fetchall()]

        # 遍历数据并插入或更新 stock_info 表
        for item in stock_data:
            dm = item.get('dm')
            mc = item.get('mc')

            if dm in stock_list:
                cursor.execute("""
                UPDATE stock_info
                SET stock_name = ?
                WHERE stock_code = ?
                """, (mc, dm))
            else:
                cursor.execute("""
                INSERT OR REPLACE INTO stock_info (stock_code, stock_name)
                VALUES (?, ?)
                """, (dm, mc))

        # 从股票列表中去除ST股票和退市股票
        cursor.execute("DELETE FROM stock_info WHERE stock_name LIKE '%ST%' OR stock_name LIKE '退市%' OR stock_name LIKE '%退'")

        # 提交更改并关闭连接
        conn.commit()
        conn.close()
        print("股票列表更新成功")
    except FileNotFoundError:
        print("config.json 文件未找到")
    except requests.RequestException as e:
        print(f"请求数据时出错: {e}")
    except json.JSONDecodeError:
        print("解析 JSON 数据时出错")
    except sqlite3.Error as e:
        print(f"数据库操作出错: {e}")


# 下载日线数据
def download_stock_data(stock_code:str, start_date:str=None):
    """
    参数:
        stock_code (str): 股票代码，如"000001.SZ"
        start_date (str)：开始日期，如果为空就更新所有日期
    返回:
        list: 包含日线数据的列表，每个元素是一个包含交易日期、开盘价、最高价、最低价、收盘价、成交量的字典
    """

    # 获取配置信息
    config = ConfigManager()
    # 构建API URL
    if start_date:
        start_date = start_date.replace("-", "")
        end_date = datetime.strftime(datetime.now().date(), "%Y-%m-%d").replace("-", "")
        # 增量更新地址
        api_url = config.get_value('stock_daily_url').replace("股票代码", stock_code).replace("您的licence", config.get_value( 'licence')) + "?&st=" + start_date +  "&et=" + end_date
    else:
        # 全量更新地址
        api_url = config.get_value('stock_daily_url').replace("股票代码", stock_code).replace("您的licence", config.get_value('licence'))

    """
    最多尝试10次请求API接口
    遇到403/404错误立即终止重试
    其他错误等待1秒后重试
    超过重试次数或遇到致命错误时返回空列表并提示下载失败"""
    i = 10
    while i > 0:
        try:
            response = requests.get(api_url, timeout=10)
            if response.status_code == 200:
                break
            elif response.status_code in (403, 404):
                i = 0
            else:
                time.sleep(0.5)
                i -= 1
        except requests.RequestException:
            time.sleep(0.5)
            i -= 1
    if i == 0:
        print(f"❌ 下载股票 {stock_code} 的数据出错...")
        error_codes = config.get_value('error_codes')
        if stock_code not in error_codes:
            if error_codes:
                error_codes += ","
            error_codes += stock_code
            config.set_value('error_codes', error_codes)
        return []

    daily_data = []
    # 解析JSON响应
    try:
        data = response.json()
        for item in data:
            # 检查sf值，如果为0才存储
            if item.get('sf', 0) == 0:
                trade_date = item['t'].split(' ')[0]
                open_price = round(float(item['o']), 2)
                high_price = round(float(item['h']), 2)
                low_price = round(float(item['l']), 2)
                close_price = round(float(item['c']), 2)
                volume = int(item['v'])
                turnover = round(float(item['a']), 2)
                prev_close = round(float(item['pc']), 2)

                # 检查数据是否有效
                if close_price > 0 and volume > 0:
                    daily_data.append({
                        'trade_date': trade_date,
                        'open_price': open_price,
                        'high_price': high_price,
                        'low_price': low_price,
                        'close_price': close_price,
                        'volume': volume,
                        'turnover': turnover,
                        'prev_close': prev_close
                    })

    except Exception as e:
        print(f"⚠️ 股票 {stock_code} 的响应数据解析出错: {e}")

    return daily_data


# 保存日线数据
def save_stock_data(stock_code:str, daily_data):
    """
    参数:
        stock_code (str): 股票代码
        daily_data (list): 日线数据列表
    """
    if not daily_data:
        print(f"⚠️ 没有数据可保存")
        return False

    conn = sqlite3.connect('db_file.db')
    cursor = conn.cursor()

    # 准备要插入的数据
    stock_code = stock_code[:6] # 存储到stock_daily日线数据表中的股票代码取前6位
    data_to_insert = [
        (stock_code, item['trade_date'], item['open_price'], item['high_price'], item['low_price'],
         item['close_price'], item['volume'], item['turnover'], item['prev_close'])
        for item in daily_data
    ]

    # 批量插入数据
    try:
        cursor.executemany("""
            INSERT OR REPLACE INTO stock_daily 
            (stock_code, trade_date, open_price, high_price, low_price, close_price, volume, turnover, prev_close)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, data_to_insert)
        conn.commit()
        print(f"✅ 成功保存 {len(daily_data)} 条日线数据")
        return True
    except sqlite3.Error as e:
        print(f"⚠️ 数据库操作失败: {str(e)}")
        conn.rollback()
        return False
    finally:
        conn.close()


# 更新日线数据
def update_stock_daily(stock_codes:list[str], start_date:str=None):
    """
    参数:
        stock_codes (list): 股票代码列表
    """
    stock_codes = StockCodeConversion.add(stock_codes) # 给股票代码列表中的所有代码添加市场后缀，与API相一致
    success_count = 0
    total_data = 0

    # 遍历股票代码列表并更新数据
    for stock_code in stock_codes:
        # 下载股票数据
        print(f"\n🔄 正在更新股票 {stock_code} 的日线数据...")
        daily_data = download_stock_data(stock_code, start_date)

        # 保存到数据库
        if daily_data:
            if save_stock_data(stock_code, daily_data):
                success_count += 1
                total_data += len(daily_data)
                print(f"✅ 股票 {stock_code} 的日线数据已更新，新增 {len(daily_data)} 条数据")
            else:
                print(f"❌ 股票 {stock_code} 的日线数据保存失败")
        else:
            print(f"ℹ️ 股票 {stock_code} 没有新的日线数据")

    print(f"\n📊 总体更新结果：")
    print(f"共更新 {success_count}/{len(stock_codes)} 只股票的数据")
    print(f"总计新增 {total_data} 条日线数据")

    return


# 计算技术指标
def calculate_technical_indicators(stock_codes, start_date=None):
    """
    计算并保存股票的技术指标数据到 stock_technical 表
    """
    stock_codes = StockCodeConversion.remove(stock_codes) # 存储到stock_technical表中的股票代码去除市场后缀

    # 为每个股票计算技术指标并保存到 stock_technical 表
    for stock_code in stock_codes:
        print(f"🔄 正在计算 {stock_code} 技术指标...")

        # 连接数据库并读取日线数据
        try:
            with sqlite3.connect('db_file.db') as conn:
                if start_date:
                    # 获取自指定日期开始的增量日线数据
                    diff_date = (datetime.now().date() - datetime.strptime(start_date, "%Y-%m-%d").date()).days + 250
                    df = pd.read_sql(
                        "SELECT * FROM stock_daily WHERE stock_code = ? ORDER BY trade_date DESC LIMIT ?",
                        conn,
                        params=(stock_code, diff_date)
                    )
                    df.sort_values(by='trade_date', ascending=True, inplace=True)
                else:
                    #  获取该股票的全量日线数据
                    df = pd.read_sql(
                        "SELECT * FROM stock_daily WHERE stock_code = ? ORDER BY trade_date",
                        conn,
                        params=(stock_code,)
                    )
        except Exception as e:
            print(f"数据库查询出错: {str(e)}")

        # 如果未读取到数据，跳过该股票
        if df.empty:
            continue

        # 计算移动平均线
        df['MA5'] = df['close_price'].rolling(window=5).mean().round(2)
        df['MA10'] = df['close_price'].rolling(window=10).mean().round(2)
        df['MA20'] = df['close_price'].rolling(window=20).mean().round(2)
        df['MA30'] = df['close_price'].rolling(window=30).mean().round(2)
        df['MA60'] = df['close_price'].rolling(window=60).mean().round(2)
        df['MA120'] = df['close_price'].rolling(window=120).mean().round(2)
        df['MA250'] = df['close_price'].rolling(window=250).mean().round(2)
        
        # 股价近250日分位数
        roll_min = df['close_price'].rolling(window=250, min_periods=1).min()
        roll_max = df['close_price'].rolling(window=250, min_periods=1).max()
        diff = roll_max - roll_min
        # 当最大值等于最小值时，设置分位数为100%
        df['Q'] = (np.where(diff != 0, (df['close_price'] - roll_min) / diff * 100, 100)).round(0)
        
        # 当前股价离近20日最小值的涨幅
        df['R'] = ((df['close_price'] / df['close_price'].rolling(window=20, min_periods=1).min()).round(2)-1) * 100

        # 计算年化波动率 (30日)
        # 计算对数收益率
        df['log_ret'] = np.log(df['close_price'] / df['close_price'].shift(1))
        # 异常值处理：过滤超过5倍标准差的大幅波动
        ret_std = df['log_ret'].rolling(90, min_periods=30).std() * 5
        df['log_ret'] = np.where(
            np.abs(df['log_ret']) > ret_std,
            np.sign(df['log_ret']) * ret_std,  # 截断异常值
            df['log_ret']
        )
        # 年化波动率计算
        df['volatility'] = (
                df['log_ret']
                .rolling(
                    window=30,
                    min_periods=24,  # 至少24个有效交易日（80%数据）
                    center=False  # 不居中计算
                )
                .std(ddof=0)  # 金融常用样本标准差ddof=1，但ddof=0更稳健
                * np.sqrt(244)  # A股年化因子（244个交易日）
        )
        # 前向填充：使用后续有效值填充前期空缺
        df['volatility'] = df['volatility'].fillna(method='bfill')
        # 最小值处理：确保波动率不低于0.5%（防止权重计算异常）
        min_volatility = 0.005  # 0.5%最小波动率
        df['volatility'] = df['volatility'].clip(lower=min_volatility)
        # 最终平滑处理（消除短期噪声）
        df['volatility'] = df['volatility'].ewm(
            span=10,  # 约10日平滑
            adjust=True  # 精确加权
        ).mean().round(2)

        # 均线粘合度
        ma_cols = ['MA5', 'MA10', 'MA20', 'MA30']
        ma_mean = df[ma_cols].mean(axis=1)
        df['cv_conv'] = df[ma_cols].std(axis=1) / ma_mean
        df['range_conv'] = (df[ma_cols].max(axis=1) - df[ma_cols].min(axis=1)) / ma_mean
        # 动态权重函数
        def dynamic_weight(vol):
            return 0.8 / (1 + 15 * vol)
        df['cv_weight'] = df['volatility'].apply(dynamic_weight)
        df['range_weight'] = 1 - df['cv_weight']
        # 组合粘合度
        df['conv'] = (df['cv_weight'] * df['cv_conv'] + df['range_weight'] * df['range_conv']).round(2)

        # 30日涨跌幅
        shifted_close = df['close_price'].shift(1)
        rolling_30 = shifted_close.rolling(window=30, min_periods=15)
        max_30 = rolling_30.max()
        min_30 = rolling_30.min()
        median_30 = rolling_30.median()
        safe_median = median_30.replace(0, np.nan)  # 或使用 0.001
        safe_median = safe_median.fillna(method='ffill')  # 向前填充NaN
        df['CHANGE30'] = ((max_30 - min_30) / safe_median).round(2)

        # 均线角度化斜率
        def calculate_slope_angle(series, window=5, smoothing=3):
            """
            专业级均线角度计算函数
            参数：
            series: 均线序列
            window: 角度计算窗口（默认5日）
            smoothing: 角度平滑周期（默认3日）
            返回：角度序列（度）
            """
            # (1) 数据预处理：填充缺失值
            series = series.ffill().bfill()
            # (2) 计算斜率（使用窗口内线性回归）
            # 创建时间索引 (x轴)
            x = np.arange(len(series))
            # 滚动窗口线性回归计算斜率
            slopes = series.rolling(
                window=window,
                min_periods=max(3, int(window * 0.6))  # 至少60%数据
            ).apply(
                lambda y: np.polyfit(x[-len(y):], y, 1)[0],
                raw=False
            )
            # (3) 计算角度（弧度转角度）
            # 使用arctan(slope)计算角度
            angles_rad = np.arctan(slopes)
            # (4) 角度平滑处理
            angles_smoothed = angles_rad.ewm(
                span=smoothing,
                adjust=True
            ).mean()
            # (5) 转换为角度（-90°到+90°）
            angles_deg = angles_smoothed * (180 / np.pi)
            # (6) 边界处理
            return angles_deg.fillna(0)
        # 应用优化后的角度计算
        ma_periods = [5, 10, 20, 30]
        for period in ma_periods:
            col_name = f'MA{period}'
            # 动态调整窗口：周期越长，角度窗口越大
            window_size = max(5, int(period / 3))
            df[f'Angle_{col_name}'] = calculate_slope_angle(
                df[col_name],
                window=window_size,
                smoothing=3
            )
        # 计算综合角度指标
        df['Angle_Composite'] = (
                0.4 * df['Angle_MA5'] +
                0.3 * df['Angle_MA10'] +
                0.2 * df['Angle_MA20'] +
                0.1 * df['Angle_MA30']
        )
        # 角度状态分类
        df['Angle_State'] = np.select(
            [
                df['Angle_Composite'] > 45,
                df['Angle_Composite'] > 30,
                df['Angle_Composite'] > 15,
                df['Angle_Composite'] < -15,
                df['Angle_Composite'] < -30,
                df['Angle_Composite'] < -45
            ],
            ['极强上涨', '强势上涨', '温和上涨', '温和下跌', '强势下跌', '极强下跌'],
            default='震荡整理'
        )

        # 计算MACD指标
        df['EMA12'] = df['close_price'].ewm(span=12, adjust=False).mean().round(2)
        df['EMA26'] = df['close_price'].ewm(span=26, adjust=False).mean().round(2)
        df['DIF'] = (df['EMA12'] - df['EMA26']).round(2)
        df['DEA'] = df['DIF'].ewm(span=9, adjust=False).mean().round(2)
        df['MACD'] = (2 * (df['DIF'] - df['DEA'])).round(2)

        # 计算布林带指标
        df['STD'] = df['close_price'].rolling(window=20).std().round(2)
        df['UB'] = (df['MA20'] + 2 * df['STD']).round(2) # BOLL上轨
        df['LB'] = (df['MA20'] - 2 * df['STD']).round(2) # BOLL下轨
        df['BOLL'] = ((df['UB'] - df['LB']) / df['MA20'] * 100).round(2) # BOLL带宽(%)

        # 计算RSI指标
        delta = df['close_price'].diff()
        gain = delta.mask(delta < 0, 0)
        loss = -delta.mask(delta > 0, 0)
        # 6日RSI - 使用Wilder平滑法
        avg_gain6 = gain.ewm(alpha=1 / 6, adjust=False).mean()
        avg_loss6 = loss.ewm(alpha=1 / 6, adjust=False).mean()
        rs6 = avg_gain6 / avg_loss6.replace(0, pd.NA)  # 避免除以零
        df['RSI6'] = (100 - (100 / (1 + rs6))).round(2)
        # 12日RSI - 使用Wilder平滑法
        avg_gain12 = gain.ewm(alpha=1 / 12, adjust=False).mean()
        avg_loss12 = loss.ewm(alpha=1 / 12, adjust=False).mean()
        rs12 = avg_gain12 / avg_loss12
        df['RSI12'] = (100 - (100 / (1 + rs12))).round(2)
        # 24日RSI - 使用Wilder平滑法
        avg_gain24 = gain.ewm(alpha=1 / 24, adjust=False).mean()
        avg_loss24 = loss.ewm(alpha=1 / 24, adjust=False).mean()
        rs24 = avg_gain24 / avg_loss24
        df['RSI24'] = (100 - (100 / (1 + rs24))).round(2)

        # 计算 RSV
        low_min = df['low_price'].rolling(window=9, min_periods=1).min()
        high_max = df['high_price'].rolling(window=9, min_periods=1).max()
        denominator = high_max - low_min
        df['RSV'] = np.where(denominator == 0, 50.0, ((df['close_price'] - low_min) / denominator * 100))
        # 初始化 K 和 D 为 50
        df['K'] = 50.0
        df['D'] = 50.0
        # 递归计算 K 和 D
        for i in range(1, len(df)):
            df.loc[df.index[i], 'K'] = round(
                (2 / 3) * df.loc[df.index[i - 1], 'K'] + (1 / 3) * df.loc[df.index[i], 'RSV'], 2)
            df.loc[df.index[i], 'D'] = round(
                (2 / 3) * df.loc[df.index[i - 1], 'D'] + (1 / 3) * df.loc[df.index[i], 'K'], 2)
        # 计算 J
        df['J'] = round(3 * df['K'] - 2 * df['D'], 2)

        # 计算CCI指标
        tp = (df['high_price'] + df['low_price'] + df['close_price']) / 3
        tp_rolling = tp.rolling(window=14)
        ma = tp_rolling.mean()
        md = tp_rolling.apply(lambda x: np.fabs(x - x.mean()).mean())
        df['CCI'] = ((tp - ma) / (0.015 * md)).round(2)

        # 计算BIAS指标
        df['BIAS5'] = ((df['close_price'] - df['MA5']) / df['MA5'] * 100).round(2)
        df['BIAS10'] = ((df['close_price'] - df['MA10']) / df['MA10'] * 100).round(2)
        df['BIAS20'] = ((df['close_price'] - df['MA20']) / df['MA20'] * 100).round(2)

        # 计算成交量指标
        df['VOL5'] = df['volume'].rolling(window=5).mean().round(2)
        df['VOL10'] = df['volume'].rolling(window=10).mean().round(2)
        df['VOL20'] = df['volume'].rolling(window=20).mean().round(2)
        df['VOL30'] = df['volume'].rolling(window=30).mean().round(2)
        df['VOL60'] = df['volume'].rolling(window=60).mean().round(2)
        df['VOL120'] = df['volume'].rolling(window=120).mean().round(2)
        df['VOL250'] = df['volume'].rolling(window=250).mean().round(2)
        df['VOLR'] = (df['volume'] / df['VOL5'].shift(1)).round(2)
        
        # 计算K线实体及上下影线
        df['body'] = (df['close_price'] - df['open_price']).abs()
        df['upper_shadow'] = df['high_price'] - df[['open_price', 'close_price']].max(axis=1)
        df['lower_shadow'] = df[['open_price', 'close_price']].min(axis=1) - df['low_price']
        
        # 卖出形态计算
        # 高位判断
        df['HIGH'] = ((df['Q'] >= 80) | (df['R'] <= 20) | (df['RSI12'] >= 70) | (df['K'] >= 80))

        # 十字星判断：
        df['is_doji'] = (
                (df['body'] / df['open_price'] < 0.01) &  # 实体极小
                ((df['upper_shadow'] > 2 * df['body']) |  # 上影线是实体2倍以上
                 (df['lower_shadow'] > 2 * df['body'])) &  # 或下影线是实体2倍以上
                (df[['upper_shadow', 'lower_shadow']].max(axis=1) > 0)  # 排除无影线情况
        )
        # 高位十字星
        df['is_high_doji'] = (df['HIGH'].shift(1) & df['is_doji'].shift(1) & (df['change_percent'] < -1))

        # 高位长影线
        df['is_high_long_shadow'] = (
                df['HIGH'] &  # 处于高位
                (df['volume'] > 1.5 * df['VOL5']) &  # 放量条件
                # 影线条件：至少有一个方向的影线显著
                (
                    # 上影线条件：上影线>5%且>2倍实体
                        (df['upper_shadow'] > 0.05 * df['open_price']) &
                        (df['upper_shadow'] > 2 * df['body']) |
                        # 下影线条件：下影线>5%且>2倍实体
                        (df['lower_shadow'] > 0.05 * df['open_price']) &
                        (df['lower_shadow'] > 2 * df['body'])
                ) &
                # 位置验证 - 确保影线出现在关键位置
                (
                    # 对于上影线：收盘价位于K线下半部
                        ((df['upper_shadow'] > df['lower_shadow']) &
                         (df['close_price'] < (df['high_price'] + df['low_price']) / 2)) |
                        # 对于下影线：收盘价位于K线上半部
                        ((df['lower_shadow'] > df['upper_shadow']) &
                         (df['close_price'] > (df['high_price'] + df['low_price']) / 2))
                ) &
                # 波动率验证 - 排除低波动K线
                ((df['high_price'] - df['low_price']) > 0.03 * df['open_price'])
        )

        # 高位放量滞涨
        df['is_volume_stagnation'] = (
                df['HIGH'] &  # 处于高位
                (df['volume'] > 1.5 * df['VOL5']) &  # 成交量大于5日均量的1.5倍
                (df['change_percent'].abs() < 1)  # 涨跌幅绝对值小于1%
        )

        # 高位放量大跌
        df['is_high_volume_decline'] = (
                df['HIGH'] &  # 处于高位
                (df['volume'] > 1.2 * df['VOL5']) &  # 成交量是5日均量的1.5倍
                (df['change_percent'] <= -3)  # 跌幅超过3%
        )
        
        # 高位放量长阴
        df['is_high_long_bearish'] = (
                df['HIGH'] &  # 处于高位
                (df['volume'] > df['VOL5'] * 1.2) &  # 成交量大于五日均量的1.5倍
                (((df['open_price'] - df['close_price']) / df['open_price']) >= 0.03)  # 阴线超过3%
        )

        # 高位三连阴
        df['is_high_triple_bearish'] = (
                df['HIGH'].shift(2) &
                (df['volume'].rolling(window=3).mean() > df['VOL5']) & (df['close_price'] < df['MA10'])&
                (
                        (
                                (df['close_price'] <= df['close_price'].shift(1)) &
                                (df['close_price'].shift(1) <= df['close_price'].shift(2)) &
                                (df['close_price'].shift(2) <= df['close_price'].shift(3))
                        )
                        |
                        (
                                (df['close_price'] <= df['open_price']) &
                                (df['close_price'].shift(1) <= df['open_price'].shift(1)) &
                                (df['close_price'].shift(2) <= df['open_price'].shift(2))
                        )
                )
        )

        # 量价顶背离
        df['is_high_volume_price'] = (
            df['HIGH'] &  # 处于高位
            (df['change_percent'] < -1) &
            (df['volume'] > 1.2 * df['volume'].shift(1))
        )

        # 删除所有包含空值的行
        df = df.dropna()

        # 准备要插入的数据
        technical_data = []
        for _, row in df.iterrows():
            technical_data.append((
                stock_code,
                row['trade_date'],
                row['MA5'],
                row['MA10'],
                row['MA20'],
                row['MA30'],
                row['MA60'],
                row['MA120'],
                row['MA250'],
                row['Q'],
                row['R'],
                row['HIGH'],
                row['volatility'],
                row['conv'],
                row['CHANGE30'],
                row['Angle_Composite'],
                row['Angle_State'],
                row['DIF'],
                row['DEA'],
                row['MACD'],
                row['BOLL'],
                row['UB'],
                row['LB'],
                row['RSI6'],
                row['RSI12'],
                row['RSI24'],
                row['K'],
                row['D'],
                row['J'],
                row['CCI'],
                row['BIAS5'],
                row['BIAS10'],
                row['BIAS20'],
                row['VOL5'],
                row['VOL10'],
                row['VOL20'],
                row['VOL30'],
                row['VOL60'],
                row['VOL120'],
                row['VOL250'],
                row['VOLR'],
                row['is_high_doji'],
                row['is_volume_stagnation'],
                row['is_high_long_shadow'],
                row['is_high_volume_decline'],
                row['is_high_long_bearish'],
                row['is_high_triple_bearish'],
                row['is_high_volume_price']
            ))

        # 批量插入或替换技术指标数据
        with sqlite3.connect('db_file.db') as conn:
            try:
                cursor = conn.cursor()
                cursor.executemany("""
                    INSERT OR REPLACE INTO stock_technical (
                    stock_code, trade_date, 
                    MA5, MA10, MA20, MA30, MA60, MA120, MA250, Q, R,
                    HIGH, volatility, conv, CHANGE30, Angle_Composite, Angle_State,
                    DIF, DEA, MACD,
                    BOLL, UB, LB,
                    RSI6, RSI12, RSI24,
                    K, D, J,
                    CCI,
                    BIAS5, BIAS10, BIAS20,
                    VOL5, VOL10, VOL20, VOL30, VOL60, VOL120, VOL250, VOLR,
                    is_high_doji, is_volume_stagnation, is_high_long_shadow, is_high_volume_decline, is_high_long_bearish, is_high_triple_bearish, is_high_volume_price
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, technical_data)
                conn.commit()  # 显式提交事务
            except sqlite3.Error as e:
                conn.rollback()  # 回滚事务
                print(f"Database error: {e}")

    print(f"✅ 成功计算并存储 {len(stock_codes)} 只股票的技术指标")


# 交易日期类（计算并存取某年的交易日期序列，判断是否为交易日，获取某个时间段的交易日期序列）
class TradeDate:
    # 生成某年的交易日历
    @staticmethod
    def set_trade_dates(year):
        # 构建获取当年节假日json数据的URL
        url = f"https://fastly.jsdelivr.net/gh/NateScarlet/holiday-cn@master/{year}.json"
        # 获取当年节假日数据（pandas日期序列）
        try:
            data = requests.get(url).json()
        except requests.RequestException as e:
            print(f"请求失败: {e}")
            return
        off_days = []
        for day in data["days"]:
            if day["isOffDay"]: off_days.append(day["date"])
        off_days = pd.to_datetime(off_days)
        # 生成当年的交易日历（pandas日期序列）
        trade_dates = pd.date_range(start=f"{year}-01-01", end=f"{year}-12-31", freq="B")
        trade_dates = trade_dates[~trade_dates.isin(off_days)]
        # 将交易日历存入数据库
        try:
            with sqlite3.connect('db_file.db') as conn:
                cursor = conn.cursor()
                cursor.executemany("INSERT OR IGNORE INTO trade_dates (trade_date) VALUES (?)",
                                   [(date.strftime("%Y-%m-%d"),) for date in trade_dates])
                conn.commit()
        except sqlite3.Error as e:
            print(f"数据库操作失败: {e}")
    
    # 获取某个时间段的交易日期序列
    @staticmethod
    def get_trade_dates(start_date, end_date):
        try:
            with sqlite3.connect('db_file.db') as conn:
                cursor = conn.cursor()

                min_year = cursor.execute("SELECT MIN(trade_date) FROM trade_dates").fetchone()[0]
                max_year = cursor.execute("SELECT MAX(trade_date) FROM trade_dates").fetchone()[0]
                if min_year is None or max_year is None:
                    for x in range(int(start_date[:4]), int(end_date[0:4]) + 1):
                        TradeDate.set_trade_dates(x)
                else:
                    min_year = min_year[:4]
                    max_year = max_year[:4]
                    if int(start_date[:4]) < int(min_year) or int(end_date[0:4]) > int(max_year):
                        for x in range(int(start_date[:4]), int(end_date[0:4]) + 1):
                            TradeDate.set_trade_dates(x)
                
                cursor.execute("SELECT trade_date FROM trade_dates WHERE trade_date BETWEEN ? AND ?",
                               (start_date, end_date))
                trade_dates = [row[0] for row in cursor.fetchall()]
                return pd.to_datetime(trade_dates)
        except sqlite3.Error as e:
            return f"操作失败: {e}"
    
    # 判断某日是否是交易日
    @staticmethod
    def is_trade_date(date):
        try:
            with sqlite3.connect('db_file.db') as conn:
                cursor = conn.cursor()

                min_year = cursor.execute("SELECT MIN(trade_date) FROM trade_dates").fetchone()[0]
                max_year = cursor.execute("SELECT MAX(trade_date) FROM trade_dates").fetchone()[0]
                if min_year is None or max_year is None:
                    TradeDate.set_trade_dates(int(date[:4]))
                else:
                    min_year = min_year[:4]
                    max_year = max_year[:4]
                    if int(date[:4]) < int(min_year) or int(date[0:4]) > int(max_year):
                        TradeDate.set_trade_dates(int(date[:4]))

                cursor.execute("SELECT trade_date FROM trade_dates WHERE trade_date =?", (date,))
                result = cursor.fetchone()
                return result is not None
        except sqlite3.Error as e:
            return f"操作失败: {e}"

# 按操作策略对股票进行诊断，返回操作建议
class Strategy:
    """
    对指定股票列表的按指定日期进行分析诊断
    参数:
        stock_code (str): 股票代码（6位数字）
        start_date (str): 开始日期，格式为 'YYYY-MM-DD'，缺失默认为今天
    返回值：
        pandas中df类型：索引为stock_code、trade_date，operate列为操作建议，1为买入，-1为卖出，0为不操作。
    """

    def __init__(self, strategy=None, stock_codes=None, start_date=None, end_date=None):
        # 默认操作测量为1
        self.strategy = int(strategy) if strategy else 1
        # 默认股票池为自选股，如果stock_codes为字符串类型则将其转换为列表类型
        if stock_codes is None:
            self.stock_codes = [code[0] for code in get_watchlist()]
        elif type(stock_codes) is str:
            self.stock_codes = [stock_codes]
        else:
            self.stock_codes = stock_codes
        self.stock_codes = StockCodeConversion.remove(self.stock_codes)
        # 如果start_date为None则默认从2010-01-01开始
        if start_date is None:
            self.start_date = "2010-01-01"
        else:
            self.start_date = start_date
        # 如果end_date为None则使用最新日期
        self.end_date = datetime.now().strftime("%Y-%m-%d") if end_date is None else end_date
        # self.result存储分析结果
        self.result = self.analyze(self.strategy, self.stock_codes, self.start_date, self.end_date)
    
    # 返回交易策略的类方法
    @classmethod
    def strategys(cls):
        try:
            with sqlite3.connect('db_file.db') as conn:
                cursor = conn.cursor()
                # 从backtest表中读取策略字段名称
                strategy = list(cursor.execute("PRAGMA table_info(backtest)").fetchall())
                strategy.pop(0)
                results = {i[0]: i[1] for i in strategy}
        except Exception as e:
            results = {1: '默认策略'}
        return results
    
    # 管理策略的类方法
    @classmethod
    def reset_strategys(cls):
        while True:
            print("\n" + "=" * 20)
            print(" 交易策略管理 ")
            print("=" * 20)
            print('1、查看策略')
            print('2、新增策略')
            print('3、修改策略')
            print('4、删除策略')
            print("0、返回")
            strategy = input('请选择您的操作：')
            print("-" * 20)
            if strategy in ['1', '2', '3', '4', '0']:
                if strategy == '0':
                    return
                match strategy:
                    case '1':
                        print(cls.strategys())
                    case '2':
                        strategy_name = input('请输入您要新增的策略名称：')
                        # 验证策略名称是否为有效的标识符
                        if not strategy_name.isidentifier():
                            print("策略名称包含非法字符，请使用有效的标识符。")
                            return
                        try:
                            with sqlite3.connect('db_file.db') as conn:
                                cursor = conn.cursor()
                                # 手动拼接 SQL 语句
                                sql = f"ALTER TABLE backtest ADD COLUMN {strategy_name} REAL;"
                                cursor.execute(sql)
                                conn.commit()
                            cls.strategys()[len(cls.strategys()) + 1] = strategy_name
                            print(f'新增策略{strategy_name}成功！')
                        except sqlite3.Error as e:
                            print(f"Database error: {e}")
                    case '3':
                        print('现有策略列表如下：', cls.strategys())
                        strategy_id = input('请输入您要修改的策略ID：')
                        try:
                            strategy_id = int(strategy_id)
                            if strategy_id not in cls.strategys():
                                print("无效的策略 ID，请重新输入。")
                                return
                        except ValueError:
                            print("输入的策略 ID 不是有效的整数，请重新输入。")
                            return
                        strategy_name = input('请输入您要修改的策略名称：')
                        # 验证策略名称是否为有效的标识符
                        if not strategy_name.isidentifier():
                            print("策略名称包含非法字符，请使用有效的标识符。")
                            return
                        try:
                            with sqlite3.connect('db_file.db') as conn:
                                cursor = conn.cursor()
                                old_col_name = cls.strategys()[strategy_id]
                                # 手动拼接 SQL 语句
                                sql = f"ALTER TABLE backtest RENAME COLUMN {old_col_name} TO {strategy_name};"
                                cursor.execute(sql)
                                conn.commit()
                            cls.strategys()[strategy_id] = strategy_name
                            print(f'修改策略{strategy_id}成功！')
                        except sqlite3.Error as e:
                            print(f"Database error: {e}")
                    case '4':
                        print('现有策略列表如下：', cls.strategys())
                        strategy_id = input('请输入您要删除的策略ID：')
                        try:
                            strategy_id = int(strategy_id)
                            if strategy_id not in cls.strategys():
                                print("无效的策略 ID，请重新输入。")
                                return
                        except ValueError:
                            print("输入的策略 ID 不是有效的整数，请重新输入。")
                            return
                        try:
                            with sqlite3.connect('db_file.db') as conn:
                                cursor = conn.cursor()
                                col_name = cls.strategys()[strategy_id]
                                # 手动拼接 SQL 语句
                                sql = f"ALTER TABLE backtest DROP COLUMN {col_name};"
                                cursor.execute(sql)
                                conn.commit()
                            # 先获取键列表，避免迭代时修改字典
                            keys = list(cls.strategys().keys())
                            for key in keys:
                                if key > strategy_id:
                                    cls.strategys()[key - 1] = cls.strategys()[key]
                            del cls.strategys()[keys[-1]]
                            print(f'删除策略{strategy_id}成功！')
                        except sqlite3.Error as e:
                            print(f"Database error: {e}")
            else:
                print('您的输入有误，请重新输入：')


    # 根据操作策略调用相应的函数
    def analyze(self,strategy, stock_codes, start_date, end_date):
        results = []  # 用于存储每个股票的结果
        # 逐个计算列表中的股票
        for stock_code in stock_codes:
            # 准备日线和指标数据，合并成stock_data
            try:
                with sqlite3.connect('db_file.db') as conn:
                    if start_date == end_date:
                        # 查询单日线和指标数据
                        trade_date = end_date
                        sql_daily = "SELECT * FROM stock_daily WHERE stock_code = ? AND trade_date <= ? ORDER BY trade_date DESC LIMIT 251"
                        sql_technical = "SELECT * FROM stock_technical WHERE stock_code = ? AND trade_date <= ? ORDER BY trade_date DESC LIMIT 251"
                        params = (stock_code, trade_date)
                        stock_daily = pd.read_sql(sql_daily, conn, index_col=['stock_code', 'trade_date'],
                                                  params=params).sort_index(ascending=True)
                        stock_technical = pd.read_sql(sql_technical, conn, index_col=['stock_code', 'trade_date'],
                                                      params=params).sort_index(ascending=True)
                        stock_data = pd.merge(stock_daily, stock_technical, how='inner',
                                              on=['stock_code', 'trade_date'])
                    else:
                        # 查询自开始至结束日期的日线和指标数据
                        sql_daily1 = "SELECT * FROM stock_daily WHERE stock_code = ? AND trade_date < ? ORDER BY trade_date DESC LIMIT 251"
                        sql_technical1 = "SELECT * FROM stock_technical WHERE stock_code = ? AND trade_date < ? ORDER BY trade_date DESC LIMIT 251"
                        params1 = (stock_code, start_date)
                        stock_daily1 = pd.read_sql(sql_daily1, conn, index_col=['stock_code', 'trade_date'],
                                                   params=params1).sort_index(ascending=True)
                        stock_technical1 = pd.read_sql(sql_technical1, conn, index_col=['stock_code', 'trade_date'],
                                                       params=params1).sort_index(ascending=True)

                        sql_daily2 = "SELECT * FROM stock_daily WHERE stock_code = ? AND trade_date BETWEEN ? AND ? ORDER BY trade_date DESC"
                        sql_technical2 = "SELECT * FROM stock_technical WHERE stock_code = ? AND trade_date BETWEEN ? AND ? ORDER BY trade_date DESC"
                        params2 = (stock_code, start_date, end_date)
                        stock_daily2 = pd.read_sql(sql_daily2, conn, index_col=['stock_code', 'trade_date'],
                                                   params=params2).sort_index(ascending=True)
                        stock_technical2 = pd.read_sql(sql_technical2, conn, index_col=['stock_code', 'trade_date'],
                                                       params=params2).sort_index(ascending=True)

                        stock_data1 = pd.merge(stock_daily1, stock_technical1, how='inner',
                                               on=['stock_code', 'trade_date']).dropna()
                        stock_data2 = pd.merge(stock_daily2, stock_technical2, how='inner',
                                               on=['stock_code', 'trade_date']).dropna()
                        stock_data = pd.concat([stock_data1, stock_data2], axis=0)
            except Exception as e:
                print(f"查询数据时出错: {e}")
                return None

            match strategy:
                case 1:
                    stock_data = self.strategy_1(stock_data)
                case 2:
                    stock_data = self.strategy_2(stock_data)
                case _:
                    return None

            # 将结果封装为DataFrame并添加到results列表中
            if (stock_data is None) or stock_data.empty:continue
            stock_data.reset_index(inplace=True)
            result = stock_data[['stock_code', 'trade_date', 'operation']]
            result = result[result['trade_date'] >= start_date]
            results.append(result)

        # 合并所有股票的结果
        try:
            results = pd.concat(results, axis=0)
            return results
        except:
            return None

    """
    # 交易策略（默认交易策略为1）：
    def strategy_2(self, stock_data):
        try:
            stock_data['operation'] = 0  # 默认观望

            # 买入条件
            condition_buy = (
                ((stock_data['MA5'] > stock_data['MA10']) & (stock_data['MA10'] > stock_data['MA20'])) |
                ((stock_data['MA5'] > stock_data['MA5'].shift(1)) &
                 (stock_data['MA10'] > stock_data['MA10'].shift(1)) &
                 (stock_data['MA20'] > stock_data['MA20'].shift(1)))
            ) & (
                stock_data['MA60'] >= stock_data['MA60'].shift(1)
            ) & (
                # 成交量放大且大于5日均量的1.5倍
                (stock_data['volume'] > (stock_data['VOL5'] * 1.5)) &
                #(stock_data['VOL5'] > stock_data['VOL5'].shift(1)) &
                (stock_data['VOL5'] > stock_data['VOL60']) &
                # MACD线向上且大于0
                (stock_data['MACD'] > 0) &
                (stock_data['MACD'] > stock_data['MACD'].shift(1)) &
                # RSI12线向上且大于50
                (stock_data['RSI12'] > stock_data['RSI12'].shift(1)) &
                (stock_data['RSI12'] >= 50) &
                (stock_data['K'] > stock_data['D']) &
                (stock_data['CHANGE30'] < 0.09) &
                # 均线角度化斜率不能大于15（避免高位买入）
                (stock_data['Angle_Composite'] > -5) &
                (stock_data['Angle_Composite'] < 10) &
                (stock_data['BOLL'] > stock_data['BOLL'].shift(1)) & (stock_data['BOLL'] >= 10) &
                (stock_data['volatility'].rolling(window=30).mean() < 0.3) &
                (stock_data['conv'].rolling(window=30).mean() <= 0.03)
            )
            stock_data.loc[condition_buy, 'operation'] = 1

            # 卖出条件 - 分组条件避免混合类型
            condition_sell_1 = (stock_data['close_price'] < stock_data['MA20'])
            condition_sell_2 = (stock_data['K'] < stock_data['D']) & ((stock_data['K'] > 80) | (stock_data['D'] > 80))
            condition_sell_3 = (stock_data['is_high_doji'] |
                                stock_data['is_high_long_shadow'] |
                                stock_data['is_volume_stagnation'] |
                                stock_data['is_high_volume_decline'] |
                                stock_data['is_high_long_bearish'] |
                                stock_data['is_high_triple_bearish'] |
                                stock_data['is_high_volume_price'])
            condition_sell = condition_sell_1 | condition_sell_2 | condition_sell_3
            stock_data.loc[condition_sell, 'operation'] = -1

            return stock_data

        except Exception as e:
            print(f"发生错误: {e}")
            return None
    """

    # 操作策略1：
    def strategy_1(self, stock_data):
        # 根据策略计算操作建议
        try:
            stock_data['operation'] = 0  # 默认观望

            # 买入条件
            condition_buy_1 = (stock_data['MA5'] > stock_data['MA10']) & (stock_data['MA10'] > stock_data['MA20'])  # 5、10、20日均线依次排列
            condition_buy_2 = (stock_data['close_price'] > stock_data['MA60']) & (stock_data['MA60'] > stock_data['MA60'].shift(1)) #& (stock_data['MA30'] > stock_data['MA30'].shift(1)) # 收盘价大于60日均线且60日、120日均线向上
            condition_buy_3 = ((stock_data['volume'] > stock_data['VOL5']) & (stock_data['VOL5'] > stock_data['VOL5'].shift(1)) & (stock_data['VOL5'] > stock_data['VOL60'])) # 当日成交量大于5日均量、5日均量向上、5日均量大于60日均量
            condition_buy_4 = ((stock_data['MACD'] > stock_data['MACD'].shift(1)) & (stock_data['MACD'] > 0)) # MACD线向上且大于0
            condition_buy_5 = ((stock_data['K'] > stock_data['D']) & ((stock_data['K'] < 80) | (stock_data['D'] < 70))) # K线大于D线且K线小于80或D线小于70
            condition_buy_6 = (stock_data['RSI12'] > 50) & (stock_data['RSI12'] < 70) # RSI12线在50-70区间
            condition_buy_7 = ~((stock_data['Q'] > 90) | (stock_data['R'] > 30)) # 否定性条件：如果股价在250日90分位或近20日涨幅大于30就不买
            condition_buy = condition_buy_1 & condition_buy_2 & condition_buy_3 & condition_buy_4 & condition_buy_5 & condition_buy_6 & condition_buy_7
            stock_data.loc[condition_buy, 'operation'] = 1

            # 卖出条件
            condition_sell_1 = ((stock_data['close_price'] < stock_data['MA20']) & (stock_data['close_price'].shift(1) < stock_data['MA20'].shift(1)))
            condition_sell_2 = (stock_data['close_price'] < stock_data['MA30'])
            condition_sell_3 = ((stock_data['MA5'] < stock_data['MA10']) & (stock_data['MA5'].shift(1) < stock_data['MA10'].shift(1)))
            condition_sell_4 = (stock_data['MA5'] < stock_data['MA10']) & (stock_data['MA10'] < stock_data['MA20'])
            condition_sell_5 = (stock_data['K'] < stock_data['D']) & (stock_data['K'] > 80) | (stock_data['D'] > 70)
            condition_sell_6 = (stock_data['is_high_doji'] |
                                stock_data['is_high_long_shadow'] |
                                stock_data['is_volume_stagnation'] |
                                stock_data['is_high_volume_decline'] |
                                stock_data['is_high_long_bearish'] |
                                stock_data['is_high_triple_bearish'] |
                                stock_data['is_high_volume_price'])
            condition_sell = condition_sell_1 | condition_sell_2 | condition_sell_3 | condition_sell_4 | condition_sell_5 #| condition_sell_6
            stock_data.loc[condition_sell, 'operation'] = -1

            return stock_data

        except Exception as e:
            print(f"发生错误: {e}")
            return None
